package Database;

import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.graphics.Color;
import android.graphics.Typeface;
import android.widget.TableLayout;
import android.widget.TableRow;
import android.widget.TextView;

import java.util.ArrayList;
import java.util.List;

import ListView_Adapter.Person_Detail;
import ListView_Adapter.Student_Detail;

public class SQLquery {

    public static final String DATABASE_NAME = "Project";
    public static final int DATABASE_VERSION = 1;
    public SQLiteDatabase db;

    public final Context context;

    // Database open/upgrade helper
    private DataBaseHelper dbHelper;

    public SQLquery(Context cont) {
        this.context = cont;
        dbHelper = new DataBaseHelper(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    public SQLquery open() throws SQLException {
        db = dbHelper.getWritableDatabase();
        return this;
    }


    public void close() {
        db.close();
    }

    public SQLiteDatabase getDatabaseInstance() {
        return db;
    }

    public void insert_student(String id, String fname, String lname, String class_name) {
        ContentValues newValues = new ContentValues();
        newValues.put("s_id", id);
        newValues.put("f_name", fname);
        newValues.put("l_name", lname);
        newValues.put("s_class", class_name);
        db.insert("student", null, newValues);
    }

    public void insert_list_class(String id_c, String id_s) {
        ContentValues newValues = new ContentValues();
        newValues.put("c_id", id_c);
        newValues.put("s_id", id_s);
        db.insert("list_class", null, newValues);
    }

    public void insert_account(String id, String password, String name, String time) {
        ContentValues newValues = new ContentValues();
        newValues.put("t_id", id);
        newValues.put("password", password);
        newValues.put("name", name);
        newValues.put("log_time", time);
        db.insert("account", null, newValues);
    }

    public void insert_class_name(String id_c, String c_name, String t_id, int per) {
        ContentValues newValues = new ContentValues();
        newValues.put("class_id", id_c);
        newValues.put("class_name", c_name);
        newValues.put("t_id", t_id);
        newValues.put("periods", per);
        db.insert("class_name", null, newValues);
    }

    public void create_intinity() {
        insert_student("10035123","Long","Nguyen Duy","DHTH6A");
        insert_student("12023123","Tru","Nguyen Cong","DHTH6D");
        insert_student("10103148","Vu","Nguyen Duy","DHTH6C");
        insert_student("10835126","Khai","Nguyen Duy","DHTH6A");
        insert_student("10351236","Tuan","Nguyen Duy","DHTH6A");
        insert_student("11550131","Huy","Nguyen Duy","DHTH6A");
        insert_student("15563199","Quang","Nguyen Duy","DHTH6A");


    }


    public void updateEntry(String old, String id, String password, String name, String time) {
        ContentValues updatedValues = new ContentValues();
        updatedValues.put("t_id", id);
        updatedValues.put("password", password);
        updatedValues.put("name", name);
        updatedValues.put("log_time", time);
        String where = "t_id = ?";
        db.update("account", updatedValues, where, new String[]{old});
    }


    public void insert_student_check(String id_student, String id_class, String date, int per) {
        String table = "check_" + id_class;

        if (!check_function(table, "id_student", id_student) && !check_function(table, "date", date)) {
            ContentValues newValues = new ContentValues();
            newValues.put("id_student", id_student);
            newValues.put("date", date);
            newValues.put("periods", per);
            db.insert(table, null, newValues);
        }
        if (check_function(table, "date", date))
            update_table_check(table, "periods", "date", date, per);
        else {
            ContentValues newValues = new ContentValues();
            newValues.put("id_student", id_student);
            newValues.put("date", date);
            newValues.put("periods", per);
            db.insert(table, null, newValues);
        }
    }

    //ham tra lai gia tri false neu gia tri nhap vao trung voi co so du lieu da do
    // Table Ten bang, column ten cot, find gia tri can tim o cot
    public boolean check_function(String table, String column, String find) {
        Cursor cursor = db.query(table, null, column + "=?", new String[]{find}, null, null, null);
        if (cursor.getCount() < 1) { // id Not Exist
            cursor.close();
            return false;
        }
        cursor.moveToFirst();
        cursor.close();
        return true;
    }

    public void update_table_check(String table, String column, String id, String id_find, int var) {
        ContentValues cv = new ContentValues();
        cv.put(column, var);
        db.update(table, cv, id + "= ?", new String[]{id_find});
    }

    public List<Person_Detail> get_list_person(String table) {


        List<Person_Detail> list = new ArrayList<Person_Detail>();

        Cursor cursor = db.rawQuery("SELECT * FROM " + table, null);
        if (cursor.moveToFirst()) {
            do {
                Person_Detail a = new Person_Detail(cursor.getString(0), cursor.getString(1), cursor.getString(2), cursor.getInt(3));
                list.add(a);
            }
            while (cursor.moveToNext());
        }
        if (cursor != null && !cursor.isClosed()) {
            cursor.close();
        }
        return list;
    }

    public List<Student_Detail> get_list_student() {
        List<Student_Detail> list = new ArrayList<Student_Detail>();

        Cursor cursor = db.rawQuery("SELECT * FROM student", null);
        ;
        if (cursor.moveToFirst()) {
            do {
                Student_Detail a = new Student_Detail(cursor.getInt(0), cursor.getString(1), cursor.getString(2), cursor.getString(3));
                list.add(a);
            }
            while (cursor.moveToNext());
        }
        if (cursor != null && !cursor.isClosed()) {
            cursor.close();
        }
        return list;
    }

    //Ve true la ko co trung id
    public boolean check_id(String id) {
        Cursor cursor = db.query("student", null, "s_id=?", new String[]{id}, null, null, null);
        if (cursor.getCount() < 1) { // id Not Exist
            cursor.close();
            return true;
        }
        cursor.moveToFirst();
        cursor.close();
        return false;
    }

    public List<String> get_table() {
        List<String> list = new ArrayList<String>();
        String query = "SELECT name FROM sqlite_master WHERE type = 'table' AND name != 'android_metadata' AND name != 'sqlite_sequence'";
        Cursor cursor = db.rawQuery(query, null);

        if (cursor.moveToFirst()) {
            do {
                String val = cursor.getString(0);
                list.add(val);
            }
            while (cursor.moveToNext());
        }
        if (cursor != null && !cursor.isClosed()) {
            cursor.close();
        }
        return list;
    }

    public void dropdown(String id) {
        String table = "check_" + id;
        db.execSQL("DROP TABLE " + table);
        String table1 = "CREATE TABLE IF NOT EXISTS " + table + "(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, id_student TEXT, date TEXT, periods INTEGER);";
        db.execSQL(table1);
    }

    public List<String> get_table_check() {
        List<String> list = new ArrayList<String>();
        String query = "SELECT name FROM sqlite_master WHERE type = 'table' AND name != 'android_metadata' AND name != 'sqlite_sequence' AND name != 'student' AND name != 'account' AND name != 'list_class' AND name != 'class_name'";
        Cursor cursor = db.rawQuery(query, null);

        if (cursor.moveToFirst()) {
            do {
                String val = cursor.getString(0);
                list.add(val);
            }
            while (cursor.moveToNext());
        }
        if (cursor != null && !cursor.isClosed()) {
            cursor.close();
        }
        return list;
    }


    public List<String> get_process() {
        List<String> list = new ArrayList<String>();
        String query = "SELECT name FROM sqlite_master WHERE type = 'table' AND name != 'android_metadata' AND name != 'sqlite_sequence'";
        Cursor cursor = db.rawQuery(query, null);

        if (cursor.moveToFirst()) {
            do {
                String val = cursor.getString(0);
                list.add(val);
            }
            while (cursor.moveToNext());
        }
        if (cursor != null && !cursor.isClosed()) {
            cursor.close();
        }
        return list;
    }

    public String check_date(String table, String date) {
        Cursor cursor = db.query(table, null, "date=?", new String[]{date}, null, null, null);
        if (cursor.getCount() < 1) { // id Not Exist
            cursor.close();
            return "NOT EXISTS";
        }
        cursor.moveToFirst();
        cursor.close();
        return "NOT GOOD";
    }

    //cap nhat thong tin cho bang table, tai o column chi dinh id voi gia tri moi la var
    public void update_table(String table, String column, String id, String id_find, String var) {
        db.execSQL("update " + table + " set " + column + " = '" + var + "'where " + id + "='" + id_find + "';");
    }


    //Tra lai gia tri pass
    public String getSinlgeEntry(String table, String id) {
        Cursor cursor = db.query(table, null, "t_id=?", new String[]{id}, null, null, null);
        if (cursor.getCount() < 1) { // UserName Not Exist
            cursor.close();
            return "NOT EXIST";
        }
        cursor.moveToFirst();
        String password = cursor.getString(cursor.getColumnIndex("password"));
        cursor.close();
        return password;
    }


    public int deleteEntry(String id) {
        String where = "t_id=?";
        return db.delete("account", where, new String[]{id});
    }


    public String getString(String selarr, String wanna) {
        Cursor cursor = db.query("account", null, "t_id=?", new String[]{selarr}, null, null, null);
        if (cursor.getCount() < 1) { // UserName Not Exist
            cursor.close();
            return "NOT EXIST";
        }
        cursor.moveToFirst();
        String code = cursor.getString(cursor.getColumnIndex(wanna));
        cursor.close();
        return code;
    }


    public Cursor cursor() {
        return db.rawQuery("SELECT * FROM student", null);
    }

    public void updatetable(String table, String user, String time) {
        db.execSQL("update " + table + " set log_time = '" + time + "'where t_id='" + user + "';");
    }


    public void format(int number) {
        dbHelper.onUpgrade(db, DATABASE_VERSION, number);
    }

    public void showdata(TableLayout a, final Activity ac, String table, String tit1, String tit2, String tit3, String tit4, String ID, String user, String name, String email) {
        Cursor c = db.rawQuery("SELECT * FROM " + table, null);
        int count = c.getCount();
        c.moveToFirst();
        TableLayout tblayout = a;

        TableRow tbrow = new TableRow(context);

        TextView v1, v2, v3, v4, v5, v6, v7, v8;
        v1 = new TextView(context);
        v1.setText(tit1);
        v1.setTextColor(Color.RED);
        v1.setTypeface(null, Typeface.BOLD);
        v1.setPadding(20, 20, 20, 20);
        tbrow.addView(v1);

        v2 = new TextView(context);
        v2.setText(tit2);
        v2.setTextColor(Color.RED);
        v2.setTypeface(null, Typeface.BOLD);
        v2.setPadding(20, 20, 20, 20);
        tbrow.addView(v2);

        v3 = new TextView(context);
        v3.setText(tit3);
        v3.setTextColor(Color.RED);
        v3.setTypeface(null, Typeface.BOLD);
        v3.setPadding(20, 20, 20, 20);
        tbrow.addView(v3);

        v8 = new TextView(context);
        v8.setText(tit4);
        v8.setTextColor(Color.RED);
        v8.setTypeface(null, Typeface.BOLD);
        v8.setPadding(20, 20, 20, 20);
        tbrow.addView(v8);

        tblayout.addView(tbrow);
        for (int i = 0; i < count; i++) {
            tbrow = new TableRow(context);
            v4 = new TextView(context);
            v4.setText(c.getString(c.getColumnIndex(ID)));

            v5 = new TextView(context);
            v5.setText(c.getString(c.getColumnIndex(user)));

            v6 = new TextView(context);
            v6.setText(c.getString(c.getColumnIndex(name)));

            v7 = new TextView(context);
            v7.setText(c.getString(c.getColumnIndex(email)));

            v4.setPadding(20, 20, 20, 20);
            v5.setPadding(20, 20, 20, 20);
            v6.setPadding(20, 20, 20, 20);

            v4.setTextColor(Color.BLACK);
            v5.setTextColor(Color.BLACK);
            v6.setTextColor(Color.BLACK);

            tbrow.addView(v4);
            tbrow.addView(v5);
            tbrow.addView(v6);
            tblayout.addView(tbrow);
            c.moveToNext();
        }
        ac.setContentView(tblayout);
        db.close();
    }


    public List<String> selectID() {
        List<String> list = new ArrayList<String>();
        Cursor cursor = this.db.query("student", new String[]{"s_id", "f_name"}, null, null, null, null, "f_name desc");

        if (cursor.moveToFirst()) {
            do {
                String val = cursor.getString(0) + "         " + cursor.getString(1);
                list.add(val);
            }
            while (cursor.moveToNext());
        }
        if (cursor != null && !cursor.isClosed()) {
            cursor.close();
        }
        return list;
    }

}

